#! /usr/bin/python
# -*- coding: cp1250 -*-

import string, re, sys
import pepe.glib as g
import pepe.p_txt.bigtxt as bt
import fnmatch

def pivotSum(inpFile="", skipHeader="0", indexC="1", indexR="2", indexV="3"):
    ''' 
    create txt pivot table 
    generate postgresql script for doing pivot on big tables/data
    
    input example
    testName	testedValue	vendorID	vendorName	uniqID
    amount.rounded_value.doc_curr.AMOUNT_IN_DOC_CURR_	900000.0			aaa-000-000-020
    amount.number_sequence.doc_curr.AMOUNT_IN_DOC_CURR_	32345.11			aaa-000-000-022

    output example:
    amount.number_sequence.doc_curr.AMOUNT_IN_DOC_CURR_	amount.repeat_number.doc_curr.AMOUNT_IN_DOC_CURR_ ...
    vid_	60	129	320	0	2811	6239	4026	0	29379	28169	2793	0	0	0	
    vid_100000	0	0	0	0	0	0	0	0	2	0	0	1	0	0	
    '''
    
    res = {}; cd = ''
    # indexes in source data of column, row, value
    ci = int(indexC) - 1; ri = int(indexR) - 1; vi = int(indexV) - 1
    # lists of columns (label) values , rows/header (label) values
    cl = []; rl = []
    
    lino = 0
    #lines = open(inpFile, 'r').readlines()
    for line in bt.lines(inpFile):
        lino += 1
        if lino == 1 and skipHeader == "1": continue

        # find out what is the column delimiter
        if cd == '': cd = g.findcoldelimiter_line(line)
        line = re.sub('\n', '', line)
        rlabel = ''; clabel = ''

        cols = string.split(line, cd)

        rlabel = cols[ri]; clabel = cols[ci]
      
        if rlabel not in rl: rl.append(rlabel)
        if clabel not in cl: cl.append(clabel) 

        if len(cols) > 3:
            if res.has_key((rlabel, clabel)):
                res[(rlabel, clabel)] += float(g.string2number(cols[vi]))
            else:
                res[(rlabel, clabel)] = float(g.string2number(cols[vi]))

    ''' Write pivot data results to file
    '''
    #of = open(outFile, 'w')
    of = open(string.split(inpFile, '.')[0] + '_pivotSum.txt', 'w')
    # shift header to left one column
    of.write('\t')
    for c in sorted (cl):
        #print c + '\t',
        of.write(str(c) + '\t')
    of.write('\n')
    for r in sorted(rl):
        #print r + '\t'
        of.write(str(r) + '\t')
        for c in sorted (cl):
            if res.has_key((r,c)):
                #print str(res[(r,c)]) + '\t',
                of.write(str(res[(r,c)]) + '\t')
            else:
                #print str('0') + '\t',
                of.write('0' + '\t')
        of.write('\n')
    of.close()

if __name__ == "__main__":
    ''' '''
    if    len(sys.argv) == 2:
        pivotSum(inpFile=sys.argv[1], skipHeader=1)
    elif  len(sys.argv) == 3:
        pivotSum(inpFile=sys.argv[1], skipHeader=sys.argv[2])
    elif  len(sys.argv) == 6:
        pivotSum(inpFile=sys.argv[1], skipHeader=sys.argv[2], indexC=sys.argv[3], indexR=sys.argv[4], indexV=sys.argv[5])
    else:
        
        print """

        pivotsum.py <file-name> [<skip-header>] [<indexC] [<indexR>] [<indexV>]

        example : pivotsum file.dat
        example : pivotsum file.dat 0 3 4 5

        file-name (mandatory)
            data filename to be processed

        skip-header (optional)
            defines whether first (header) row in data should be skipped
            default 0 (first row is not skipped, because its data) or
                    1 (skip header row)

        indexC (optional)
            index of column in source data
            default 1 (first column values  as pivot columns [as header])
            
        indexR (optional)
            index of row label in source data
            default 2 (second column values as pivot rows)
           
        indexV (optional)
            index of value column in source data
            default 3 (third column data will be summed in pivot)
        """
